﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace pttk_htt_hr.DAO
{
    class DBMS
    {
        private string _connectionstring = "";
        private SqlConnection _conn = null;

        public string ConnectionString
        {
            get
            {
                return _connectionstring;
            }
            set
            {
                _connectionstring = value;
            }
        }

        public DBMS()
        {
            _connectionstring = @"Data Source=LONGHUYNH\SQLEXPRESSR2;Initial Catalog=pttkpm_qlks;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
        }
        //Data Source=LONGHUYNH\SQLEXPRESSR2;Initial Catalog=pttkhttt-huongrung;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False

        public void Connect()
        {
            try
            {
                _conn = new SqlConnection(_connectionstring);
                _conn.Open();
            }
            catch (Exception e)
            {
                throw new Exception("Không kết nối được cơ sở dữ liệu !!! " + e.Message);
            }
        }

        public void Disconnect()
        {
            _conn.Close();
            _conn = null;
        }

        public bool IsConnect()
        {
            return (_conn != null);
        }

  #region SQL Direct
        #region SQLDirect_Select_SqlDataReader
        public SqlDataReader ExecuteQuery(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteReader();

        }
        #endregion

        #region SQLDirect_Select_DataSet
        public DataSet ExcuteQueryDataSet(string strSQL)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds;
        }
        #endregion

        #region SQLDirect_Select_DataTable
        public DataTable ExcuteQueryDataTable(string strSQL)
        {
            DataSet ds = ExcuteQueryDataSet(strSQL);
            return ds.Tables[0];
        }
        #endregion


        #region SQLDirect_Select_Object
        public object ExecuteScalar(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteScalar();
        }
        #endregion

        #region SQLDirect_InsertUpdate_Int
        public int ExecuteNoneQuery(string strSQL)
        {

            SqlCommand _command = new SqlCommand(strSQL, _conn);
            int i = _command.ExecuteNonQuery();
            return i;
        }
        #endregion
  #endregion



  #region Procedure Store

        #region ProcedureStore_Select_DataTable
        public DataTable ExcuteProcedure_Select_DataTable(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region ProcedureStore_GetCurrentID
        public int ExcuteProcedure_GetCurrentID(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                return Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region ProcedureStore_NextID
        public int ExcuteProcedure_NextID(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                return Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }
        }
        #endregion

        #region ProcedureStore_Count
        public int ExcuteProcedure_Count(string procName, SqlParameter[] parameters)
        {
            int row_count = 0;
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }
               
                row_count = Convert.ToInt32(command.ExecuteScalar());
                command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
            }

            return row_count;
        }
        #endregion

        #region ProcedureStore_InsertUpdate
        public void ExcuteProcedure_InsertUpdate(string procName, SqlParameter[] parameters)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi function DataService.ExcuteProcedure()!!! \n" + ex.Message);
            }
        }
        #endregion

        #region ProcedureStore_InsertUpdate_Int
        public int ExcuteProcedure_InsertUpdateDelete_Int(string procName, SqlParameter[] parameters)
        {
            int result = 0; // OK
            try
            {
                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                /*
                 * returnVal = 0 if OK, 
                 * else -1 error
                 */
                SqlParameter returnVal = new SqlParameter("returnVal", SqlDbType.Int);
                returnVal.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnVal);
                command.ExecuteNonQuery();

                // Set result of execute state of sp
                result = Convert.ToInt32(returnVal.Value);
                command.Parameters.Clear();
            }
            catch (Exception ex)
            {
                result = -1;
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() \n" + ex.Message);
            }

            return result;
        }
        #endregion

  #endregion

    }
}
